﻿CREATE PROCEDURE [dbo].[sp_Get_ZipsTypeCountForTest]
	@Year varchar(4),
	@Month varchar(2)
AS
	select 'Grand Total', sum(case when os.ActiveCount is null then 0 else os.ActiveCount end) as ActiveCount,
	sum(case when os.LapsedCount is null then 0 else os.LapsedCount end) as LapsedCount,
	sum(case when os.BusinessCount is null then 0 else os.BusinessCount end) as BusinessCount,
	sum(case when os.NeighborCount is null then 0 else os.NeighborCount end) as NeighborCount,
	sum(ActiveCount + BusinessCount + LapsedCount + NeighborCount) as TotalCount
	from dbo.Accounts a
	left join
	(
		select o.AccountID, 
		sum(case when oz.Type = 2 then 0 else zi.ActiveStopCount end) as ActiveCount,
		sum(case when oz.Type = 2 then 0 else zi.LapsedStopCount end) as LapsedCount, 
		sum(case when oz.Type = 1 then 0 else zi.BusinesLeadCount end) as BusinessCount,
		sum(case when oz.Type = 2 then 0 else zi.NeighborLeadCount end) as NeighborCount
		from dbo.Orders o
		inner join dbo.OrderZip oz on o.OrderID = oz.OrderID
		and o.PeroidYear =@Year and o.PeroidMonth = @Month and o.IsPurchased = 1
		inner join dbo.ZipInfo zi on zi.ZipID = oz.ZipID
		group by o.OrderID, o.AccountID
	)os
	on a.AccountID = os.AccountID
	where a.AccountType = 0